<?php

namespace SLN0002\DAO;

use Home\DAO\BizConfigDAO;
use Home\DAO\OrgDAO;
use Home\DAO\PSIBaseExDAO;

/**
 * 记账凭证 DAO
 *
 * @author PSI
 * @copyright 2015 - present
 * @license GPL v3
 */
class VoucherDAO extends PSIBaseExDAO
{

  private function voucherStatusCodeToName($code)
  {
    switch ($code) {
      case 0:
        return "待复核";
      case 1000:
        return "已复核未记账";
      case 2000:
        return "已记明细账(但未记总分类账)";
      case 3000:
        return "已记总分类账(记账完毕)";
      default:
        return "[无效的code]";
    }
  }

  /**
   * 凭证主表列表
   */
  public function voucherList($params)
  {
    $db = $this->db;

    $loginUserId = $params["loginUserId"];
    if ($this->loginUserIdNotExists($loginUserId)) {
      return $this->emptyResult();
    }

    $orgId = $params["orgId"];
    $year = $params["year"];
    $month = $params["month"];
    $ref = $params["ref"];
    $inputUserId = $params["inputUserId"];
    $commitUserId = $params["commitUserId"];
    $status = $params["status"];

    $start = $params["start"];
    $limit = $params["limit"];

    $sql = "select id, v_status, v_word, ref, v_dt, input_user_name, input_dt,
              confirm_user_name, confirm_dt, gl_user_name, gl_dt, charge_user_name,
              cash_user_name, biz_user_name, bill_number, company_name,
              gl_dt_2, gl_user_name_2
            from t_voucher
            where (company_id = '%s') and (v_year = %d) and (v_month = %d)";
    $queryParams = [];
    $queryParams[] = $orgId;
    $queryParams[] = $year;
    $queryParams[] = $month;
    if ($ref) {
      $sql .= " and (ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($inputUserId) {
      $sql .= " and (input_user_id = '%s') ";
      $queryParams[] = $inputUserId;
    }
    if ($commitUserId) {
      $sql .= " and (confirm_user_id = '%s') ";
      $queryParams[] = $commitUserId;
    }
    if ($status != -1) {
      $sql .= " and (v_status = %d) ";
      $queryParams[] = $status;
    }

    $sql .= " order by v_word, v_ref desc
              limit %d, %d";
    $queryParams[] = $start;
    $queryParams[] = $limit;
    $result = [];
    $data = $db->query($sql, $queryParams);
    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "voucherStatus" => $this->voucherStatusCodeToName($v["v_status"]),
        "word" => $v["v_word"],
        "ref" => $v["ref"],
        "voucherDT" => $this->toYMD($v["v_dt"]),
        "inputUserName" => $v["input_user_name"],
        "inputDT" => $v["input_dt"],
        "confirmUserName" => $v["confirm_user_name"],
        "confirmDT" => $v["confirm_dt"],
        "glUserName" => $v["gl_user_name"],
        "glDT" => $v["gl_dt"],
        "glUserName2" => $v["gl_user_name_2"],
        "glDT2" => $v["gl_dt_2"],
        "chargeUserName" => $v["charge_user_name"],
        "cashUserName" => $v["cash_user_name"],
        "bizUserName" => $v["biz_user_name"],
        "billNumber" => $v["bill_number"] == 0 ? "" : $v["bill_number"],
        "orgName" => $v["company_name"],
      ];
    }

    $sql = "select count(*) as cnt
              from t_voucher
              where (company_id = '%s') and (v_year = %d) and (v_month = %d)";
    $queryParams = [];
    $queryParams[] = $orgId;
    $queryParams[] = $year;
    $queryParams[] = $month;
    if ($ref) {
      $sql .= " and (ref like '%s') ";
      $queryParams[] = "%{$ref}%";
    }
    if ($inputUserId) {
      $sql .= " and (input_user_id = '%s') ";
      $queryParams[] = $inputUserId;
    }
    if ($commitUserId) {
      $sql .= " and (confirm_user_id = '%s') ";
      $queryParams[] = $commitUserId;
    }
    if ($status != -1) {
      $sql .= " and (v_status = %d) ";
      $queryParams[] = $status;
    }

    $data = $db->query($sql, $queryParams);
    $cnt = $data[0]["cnt"];

    return [
      "dataList" => $result,
      "totalCount" => $cnt,
    ];
  }

  /**
   * 查询凭证字列表
   */
  function queryVoucherWord($params)
  {
    $db = $this->db;

    $orgId = $params["orgId"];

    $sql = "select id, name
            from t_sln0002_ct_voucher_word
            where company_id = '%s' and record_status = 1000
            order by code";
    $data = $db->query($sql, $orgId);

    $result = [];
    foreach ($data as $v) {
      $result[] = [
        "id" => $v["id"],
        "name" => $v["name"],
      ];
    }
    if (count($result) === 0) {
      $result[] = [
        "id" => "-1",
        "name" => "[不使用凭证字]",
      ];
    }

    return $result;
  }

  /**
   * 查询账样的扩展项
   */
  function queryFmtEx($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $result = [];

    $sql = "select id 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }

    $fmtId = $data[0]["id"];

    $sql = "select id, caption, voucher_input,
              code_table_name, voucher_input_xtype, db_field_decimal,
              voucher_input_colspan, voucher_input_width,
              col_cpm
            from t_acc_fmt_cols
            where fmt_id = '%s' and sys_col = 0 and voucher_input != 5
              and col_category != 2
            order by voucher_input_show_order ";
    $data = $db->query($sql, $fmtId);
    foreach ($data as $v) {
      $voucherInput = $v["voucher_input"];
      $fid = "";
      if ($voucherInput == 2) {
        // 码表录入
        $codeTableName = $v["code_table_name"];

        $sql = "select fid
                from t_code_table_md
                where table_name = '%s' ";
        $d = $db->query($sql, $codeTableName);
        $fid = $d[0]["fid"];
      }

      $result[] = [
        "id" => $v["id"],
        "caption" => $v["caption"],
        "voucherInput" => $voucherInput,
        "voucherInputXtype" => $v["voucher_input_xtype"],
        "fieldDec" => $v["db_field_decimal"],
        "fid" => $fid,
        "colspan" => $v["voucher_input_colspan"],
        "width" => $v["voucher_input_width"],
        "fmtCPM" => $v["col_cpm"],
      ];
    }

    return $result;
  }

  /**
   * 新建凭证
   */
  public function addVoucher(&$bill)
  {
    $db = $this->db;

    $loginUserId = $bill["loginUserId"];
    $dataOrg = $bill["dataOrg"];

    $createdBy = $bill["createdBy"];
    if (!$createdBy) {
      $createdBy = 0;
    }

    $orgId = $bill["orgId"];
    $wordId = $bill["wordId"];
    $voucherDT = $bill["voucherDT"];
    $chargeUserId = $bill["chargeUserId"];
    $cashUserId = $bill["cashUserId"];
    $bizUserId = $bill["bizUserId"];
    $billNumber = $bill["billNumber"];
    if ($billNumber < 0) {
      $billNumber = 0;
    }

    $ignoreBalance = intval($bill["ignoreBalance"]) == 1;
    if (!$ignoreBalance) {
      // 检查分录金额合计是否相等
      $items = $bill["items"];
      $dbSum = 0;
      $crSum = 0;
      foreach ($items as $v) {
        $debit = floatval($v["debit"]);
        $credit = floatval($v["credit"]);

        $dbSum += $debit;
        $crSum += $credit;
      }
      if ($dbSum != $crSum) {
        return $this->bad("分录金额合计不平，无法保存凭证");
      }
    }

    // 检查orgId
    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $orgId);
    if (!$data) {
      return $this->badParam("orgId");
    }
    $orgName = $data[0]["name"];

    // 检查凭证字
    $word = "";
    if ($wordId != "-1") {
      $sql = "select name
              from t_sln0002_ct_voucher_word
              where id = '%s' and record_status = 1000";
      $data = $db->query($sql, $wordId);
      if (!$data) {
        return $this->badParam("wordId");
      }

      $word = $data[0]["name"];
    }

    $dt = strtotime($voucherDT);
    $voucherYear = date("Y", $dt);
    $voucherMonth = date("m", $dt);

    // 判断输入的会计期间是否是当前可以记账的会计期间
    $sql = "select acc_gl_closed, acc_detail_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $orgId, $voucherYear, $voucherMonth);
    if (!$data) {
      return $this->bad("会计期间[{$voucherYear}年-{$voucherMonth}月]还没有初始化，无法新建凭证");
    }
    $glClosed = $data[0]["acc_gl_closed"] == 1;
    $detailClosed = $data[0]["acc_detail_closed"] == 1;
    if ($glClosed) {
      return $this->bad("会计期间[{$voucherYear}年-{$voucherMonth}月]的总账已经关账，不能再新建凭证了");
    }
    if ($detailClosed) {
      return $this->bad("会计期间[{$voucherYear}年-{$voucherMonth}月]的明细账已经关账，不能再新建凭证了");
    }
    // 凭证不能跨月创建，例如：上个月没有结账，就不能创建本月的凭证
    $sql = "select count(*) as cnt
            from t_acc_period
            where company_id = '%s' and (acc_gl_closed = 1 or acc_detail_closed = 1)";
    $data = $db->query($sql, $orgId);
    $cnt = $data[0]["cnt"];
    // --------------------------------------------------------------
    // $cnt = 0:意味着只是初始化了会计期间，没有其他操作，这时候是可以创建凭证的
    // 在实际中$cnt = 0是不会存在的，因为在【期初建账】模块开发完成后，
    // 总账系统的初始化工作通过【期初建账】录入期初数据，同时会更改会计期间的标志数据
    // TODO 在【期初建账】完成后，加上判断：$cnt =0的时候，不让新建凭证
    // --------------------------------------------------------------
    // 所以只需要处理 $cnt > 0 的情景
    if ($cnt > 0) {
      // 查询上一个月是否已经结账
      // 分为两者情况：1）上一个月是本年内的；2）上一个月是去年的12月
      if ($voucherMonth > 1) {
        // 上一个月是本年内
        $lastYear = $voucherYear;
        $lastMonth = $voucherMonth - 1;
        $sql = "select period_closed
                from t_acc_period
                where company_id = '%s' and acc_year = %d and acc_month = %d";
        $data = $db->query($sql, $orgId, $lastYear, $lastMonth);
        if (!$data) {
          // 这个情况应该是不会出现
          return $this->bad("会计期间[{$lastYear}年-{$lastMonth}月]还没有初始化，无法新建凭证");
        }
        $periodClosed = $data[0]["period_closed"] == 1;
        if (!$periodClosed) {
          return $this->bad("因为上月还没有做结账处理，所以无法新建本月的记账凭证");
        }
      } else {
        // 上一个月是去年12月
        // 需要判断去年是否完成了年终结转
        $lastYear = $voucherYear - 1;
        $lastMonth = 12;
        $sql = "select year_forward
                from t_acc_period
                where compnay_id = '%s' and acc_year = %d and acc_month = %d";
        $data = $db->query($sql, $orgId);
        // $data == null的场景：去年并没有启用总账系统，是从今年1月开始应用总账系统的
        if ($data) {
          $yearForward = $data[0]["year_forward"] == 1;
          if (!$yearForward) {
            return $this->bad("因为去年还没有做年终结转，所以无法创建今年的记账凭证");
          }
        }
      }
    }

    // 生成凭证号
    // 在一个会计期间内，凭证号要求连续不能有断号
    // 常见的一个场景是：用户删除了某张中间号的凭证，再录入新凭证的时候，需要把断号取出来
    // 例如： 10、11、12、13，用户删除了11号凭证，再新建凭证的时候，就应该生成11号
    $sql = "select v_ref
            from t_voucher
            where company_id = '%s' and v_year = %d and v_month = %d
              and v_word = '%s'
            order by v_ref";
    $data = $db->query($sql, $orgId, $voucherYear, $voucherMonth, $word);
    $newVRef = 1;
    $found = false;
    foreach ($data as $i => $v) {
      $vRef = $v["v_ref"];
      $index = $i + 1;
      if ($vRef != $index) {
        // 出现断号了
        $found = true;
        $newVRef = $index;
        break;
      }
    }
    if (!$found) {
      // 没有断号，新的号就延续+1
      $newVRef = count($data) + 1;
    }
    $ref = "{$word}字第{$newVRef}号";

    // 会计主管
    $chargeUserName = "";
    if ($chargeUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $chargeUserId);
      if (!$data) {
        return $this->badParam("chargeUserId");
      }
      $chargeUserName = $data[0]["name"];
    } else {
      $chargeUserId = "";
    }

    // 出纳
    $cashUserName = "";
    if ($cashUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $cashUserId);
      if (!$data) {
        return $this->badParam("cashUserId");
      }
      $cashUserName = $data[0]["name"];
    } else {
      $cashUserId = "";
    }

    if ($chargeUserId && $cashUserId) {
      if ($chargeUserId == $cashUserId) {
        return $this->bad("会计主管和出纳不能是同一个人");
      }
    }

    // 业务负责人
    $bizUserName = "";
    if ($bizUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $bizUserId);
      if (!$data) {
        return $this->badParam("bizUserId");
      }
      $bizUserName = $data[0]["name"];
    } else {
      $bizUserId = "";
    }

    // 制单人
    $sql = "select name from t_user where id = '%s' ";
    $data = $db->query($sql, $loginUserId);
    if (!$data) {
      return $this->badParam("loginUserId");
    }
    $inputUserName = $data[0]["name"];


    // 主表
    $id = $this->newId();
    $sql = "insert into t_voucher(
              id, v_status, v_year, v_month, v_dt, 
              v_word, v_ref, ref, input_user_id, input_user_name,
              input_dt, charge_user_id, charge_user_name, cash_user_id, cash_user_name,
              bill_number, company_id, biz_user_id, biz_user_name, company_name,
              data_org, created_by
            )
            values (
              '%s', 0, %d, %d, '%s',
              '%s', %d, '%s', '%s', '%s',
              now(), '%s', '%s', '%s', '%s',
              %d, '%s', '%s', '%s', '%s',
              '%s', %d 
            )";
    $rc = $db->execute(
      $sql,
      // ----
      $id,
      $voucherYear,
      $voucherMonth,
      $voucherDT,
      // ----
      $word,
      $newVRef,
      $ref,
      $loginUserId,
      $inputUserName,
      // ----
      $chargeUserId,
      $chargeUserName,
      $cashUserId,
      $cashUserName,
      // ----
      $billNumber,
      $orgId,
      $bizUserId,
      $bizUserName,
      $orgName,
      // ----
      $dataOrg,
      $createdBy,
    );
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 分录
    $items = $bill["items"];
    foreach ($items as $i => $v) {
      $detailId = $this->newId();
      $subjectCode = $v["subjectCode"];
      $debit = $v["debit"];
      $credit = $v["credit"];
      $summary = $v["summary"];

      if (!$subjectCode) {
        continue;
      }

      // 检查科目是否为末级科目
      $sql = "select is_leaf
              from t_subject
              where code = '%s' and company_id = '%s' ";
      $data = $db->query($sql, $subjectCode, $orgId);
      $isLeaf = $data[0]["is_leaf"] == 1;
      if (!$isLeaf) {
        return $this->bad("科目[{$subjectCode}]不是末级科目，无法创建分录");
      }

      $sql = "insert into t_voucher_detail(id, voucher_id, subject, debit, credit, summary, show_order)
              values ('%s', '%s', '%s', if(%f = 0, null, %f), if(%f = 0, null, %f), '%s', %d)";
      $rc = $db->execute($sql, $detailId, $id, $subjectCode, $debit, $debit, $credit, $credit, $summary, $i);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      // 分录附加项
      $sql = "select voucher_db_table_name
              from t_acc_fmt
              where subject_code = '%s' and company_id = '%s' ";
      $data = $db->query($sql, $subjectCode, $orgId);
      if (!$data) {
        return $this->bad("科目[{$subjectCode}]没有账样元数据");
      }
      $exTableName = $data[0]["voucher_db_table_name"];

      // 这里用的算法是：
      // 不管有没有分录扩展项，都在凭证分录扩展表中插入id数据
      // 之后实际插入数据的时候，就改为update该条记录
      $sql = "insert into {$exTableName} (id, voucher_id) values ('%s', '%s')";
      $rc = $db->execute($sql, $detailId, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      $itemEx = $v["itemEx"];
      if (count($itemEx) == 0) {
        continue;
      }
      foreach ($itemEx as $x) {
        $colId = $x["colId"];
        $colValue = $x["value"];
        $colCodeId = $x["codeId"];
        $colCodeValue = $x["codeValue"];
        $colNameValue = $x["nameValue"];

        $sql = "select db_field_name, voucher_input
                from t_acc_fmt_cols
                where id = '%s' ";
        $data = $db->query($sql, $colId);
        if (!$data) {
          // 如果执行到这里，则是莫名bug
          continue;
        }

        $fieldName = $data[0]["db_field_name"];
        $voucherInput = $data[0]["voucher_input"];
        if ($voucherInput == 1) {
          // 直接录入
          $sql = "update {$exTableName}
                    set {$fieldName} = '%s'
                  where id = '%s' ";
          $rc = $db->execute($sql, $colValue, $detailId);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        } elseif ($voucherInput == 2) {
          // 码表
          $sql = "update {$exTableName}
                    set {$fieldName} = '%s', {$fieldName}_name = '%s', {$fieldName}_id = '%s'
                  where id = '%s' ";
          $rc = $db->execute($sql, $colCodeValue, $colNameValue, $colCodeId, $detailId);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        }
      }
    }

    // 业务日志
    $log = "新建记账凭证：凭证号 - {$ref}，组织机构 - {$orgName}";
    $bill["log"] = $log;

    // 操作成功
    $bill["id"] = $id;
    return null;
  }

  /**
   * 编辑凭证
   */
  public function updateVoucher(&$bill)
  {
    $db = $this->db;

    // 凭证id
    $id = $bill["id"];

    $chargeUserId = $bill["chargeUserId"];
    $cashUserId = $bill["cashUserId"];
    $bizUserId = $bill["bizUserId"];
    $billNumber = $bill["billNumber"];
    if ($billNumber < 0) {
      $billNumber = 0;
    }

    $ignoreBalance = intval($bill["ignoreBalance"]) == 1;
    if (!$ignoreBalance) {
      // 检查分录金额合计是否相等
      $items = $bill["items"];
      $dbSum = 0;
      $crSum = 0;
      foreach ($items as $v) {
        $debit = floatval($v["debit"]);
        $credit = floatval($v["credit"]);

        $dbSum += $debit;
        $crSum += $credit;
      }
      if ($dbSum != $crSum) {
        return $this->bad("分录金额合计不平，无法保存凭证");
      }
    }

    // 会计主管
    $chargeUserName = "";
    if ($chargeUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $chargeUserId);
      if (!$data) {
        return $this->badParam("chargeUserId");
      }
      $chargeUserName = $data[0]["name"];
    } else {
      $chargeUserId = "";
    }

    // 出纳
    $cashUserName = "";
    if ($cashUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $cashUserId);
      if (!$data) {
        return $this->badParam("cashUserId");
      }
      $cashUserName = $data[0]["name"];
    } else {
      $cashUserId = "";
    }

    if ($chargeUserId && $cashUserId) {
      if ($chargeUserId == $cashUserId) {
        return $this->bad("会计主管和出纳不能是同一个人");
      }
    }

    // 业务负责人
    $bizUserName = "";
    if ($bizUserId) {
      $sql = "select name from t_user where id = '%s' ";
      $data = $db->query($sql, $bizUserId);
      if (!$data) {
        return $this->badParam("bizUserId");
      }
      $bizUserName = $data[0]["name"];
    } else {
      $bizUserId = "";
    }

    $sql = "select v_status, v_year, v_month, company_id, ref
            from t_voucher
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要编辑的记账凭证不存在");
    }
    $voucherStatus = $data[0]["v_status"];
    if ($voucherStatus > 0) {
      return $this->bad("不能编辑已经复核的记账凭证");
    }
    $voucherYear = $data[0]["v_year"];
    $voucherMonth = $data[0]["v_month"];
    // 校验凭证日期
    // 因为这是编辑凭证，要保证凭证日期在其录入的期间内
    // 不然的话，凭证号就不对了，因为凭证号在一个期间内连续
    $voucherDT = $bill["voucherDT"];
    $dt = strtotime($voucherDT);
    $year = date("Y", $dt);
    $month = date("m", $dt);
    if ($year != $voucherYear || $month != $voucherMonth) {
      return $this->bad("凭证日期[{$voucherDT}]不在会计期间[{$voucherYear}年{$voucherMonth}月]内");
    }
    $ref = $data[0]["ref"];

    $companyId = $data[0]["company_id"];
    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    // 1）更新凭证主表
    $sql = "update t_voucher
            set v_dt = '%s', charge_user_id = '%s', charge_user_name = '%s', 
              cash_user_id = '%s', cash_user_name = '%s', biz_user_id = '%s', biz_user_name = '%s',
              bill_number = %d, company_name = '%s'
            where id = '%s' ";
    $rc = $db->execute(
      $sql,
      $voucherDT,
      $chargeUserId,
      $chargeUserName,
      $cashUserId,
      $cashUserName,
      $bizUserId,
      $bizUserName,
      $billNumber,
      $companyName,
      $id
    );
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 2）凭证编辑的分录处理：先删除后重新插入
    // 2.1 删除凭证分录附加项和凭证分录
    // 2.2 重新插入凭证分录和分录附加项目

    // 2.1
    // 删除分录附加项
    $sql = "select subject
            from t_voucher_detail
            where voucher_id = '%s' 
            order by show_order";
    $detailData = $db->query($sql, $id);
    foreach ($detailData as $v) {
      $subjectCode = $v["subject"];

      // 查询账样获取分录附加项的数据库表名
      $sql = "select voucher_db_table_name
              from t_acc_fmt
              where company_id = '%s' and subject_code = '%s' ";
      $data = $db->query($sql, $companyId, $subjectCode);
      if (!$data) {
        // 出莫名的bug了
        return $this->badParam("companyId - subjectCode");
      }

      $exTableName = $data[0]["voucher_db_table_name"];

      $sql = "delete from {$exTableName} where voucher_id = '%s' ";
      $rc = $db->execute($sql, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    // 删除凭证分录
    $sql = "delete from t_voucher_detail where voucher_id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 2.2
    // 分录
    $items = $bill["items"];
    foreach ($items as $i => $v) {
      $detailId = $this->newId();
      $subjectCode = $v["subjectCode"];
      $debit = $v["debit"];
      $credit = $v["credit"];
      $summary = $v["summary"];

      if (!$subjectCode) {
        continue;
      }

      // 检查科目是否为末级科目
      $sql = "select is_leaf
              from t_subject
              where code = '%s' and company_id = '%s' ";
      $data = $db->query($sql, $subjectCode, $companyId);
      $isLeaf = $data[0]["is_leaf"] == 1;
      if (!$isLeaf) {
        return $this->bad("科目[{$subjectCode}]不是末级科目，无法创建分录");
      }

      $sql = "insert into t_voucher_detail(id, voucher_id, subject, debit, credit, summary, show_order)
              values ('%s', '%s', '%s', if(%f = 0, null, %f), if(%f = 0, null, %f), '%s', %d)";
      $rc = $db->execute($sql, $detailId, $id, $subjectCode, $debit, $debit, $credit, $credit, $summary, $i);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      // 分录附加项
      $sql = "select voucher_db_table_name
              from t_acc_fmt
              where subject_code = '%s' and company_id = '%s' ";
      $data = $db->query($sql, $subjectCode, $companyId);
      if (!$data) {
        return $this->bad("科目[{$subjectCode}]没有账样元数据");
      }
      $exTableName = $data[0]["voucher_db_table_name"];

      // 这里用的算法是：
      // 不管有没有分录扩展项，都在凭证分录扩展表中插入id数据
      // 之后实际插入数据的时候，就改为update该条记录
      $sql = "insert into {$exTableName} (id, voucher_id) values ('%s', '%s')";
      $rc = $db->execute($sql, $detailId, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      $itemEx = $v["itemEx"];
      if (count($itemEx) == 0) {
        continue;
      }
      foreach ($itemEx as $x) {
        $colId = $x["colId"];
        $colValue = $x["value"];
        $colCodeId = $x["codeId"];
        $colCodeValue = $x["codeValue"];
        $colNameValue = $x["nameValue"];

        $sql = "select db_field_name, voucher_input
                from t_acc_fmt_cols
                where id = '%s' ";
        $data = $db->query($sql, $colId);
        if (!$data) {
          // 如果执行到这里，则是莫名bug
          continue;
        }

        $fieldName = $data[0]["db_field_name"];
        $voucherInput = $data[0]["voucher_input"];
        if ($voucherInput == 1) {
          // 直接录入
          $sql = "update {$exTableName}
                    set {$fieldName} = '%s'
                  where id = '%s' ";
          $rc = $db->execute($sql, $colValue, $detailId);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        } elseif ($voucherInput == 2) {
          // 码表
          $sql = "update {$exTableName}
                    set {$fieldName} = '%s', {$fieldName}_name = '%s', {$fieldName}_id = '%s'
                  where id = '%s' ";
          $rc = $db->execute($sql, $colCodeValue, $colNameValue, $colCodeId, $detailId);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        }
      }
    }

    // 业务日志
    $log = "新建记账凭证：凭证号 - {$ref}，组织机构 - {$companyName}";
    $bill["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 凭证分录
   */
  public function voucherDetailList($params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $result = [];
    $sql = "select company_id from t_voucher where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $result;
    }
    $companyId = $data[0]["company_id"];

    // 查询物料小数位数
    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    $sql = "select v.id, v.subject, v.summary, v.debit, v.credit, s.name
            from t_voucher_detail v, t_subject s
            where v.voucher_id = '%s' and v.subject = s.code and s.company_id = '%s'
            order by v.show_order";
    $data = $db->query($sql, $id, $companyId);

    foreach ($data as $v) {
      $detailId = $v["id"];
      $subjectCode = $v["subject"];
      $subjectName = $v["name"];
      $summary = $v["summary"];
      $debit = $v["debit"];
      $credit = $v["credit"];

      $sql = "select id, voucher_db_table_name 
              from t_acc_fmt 
              where company_id = '%s' and subject_code = '%s' ";
      $data = $db->query($sql, $companyId, $subjectCode);
      if (!$data) {
        // 执行到这里应该就是莫名的bug
        continue;
      }

      $fmtId = $data[0]["id"];
      $exTableName = $data[0]["voucher_db_table_name"];

      $fmtCols = [];
      $fmtDBFields = [];
      $sql = "select caption, db_field_name, db_field_type, voucher_input,
                voucher_input_colspan, voucher_input_width
            from t_acc_fmt_cols
            where fmt_id = '%s' and sys_col = 0 and col_category != 2
            order by voucher_input_show_order ";
      $data = $db->query($sql, $fmtId);
      foreach ($data as $vCol) {
        $fmtCols[] = [
          "caption" => $vCol["caption"],
          "colspan" => $vCol["voucher_input_colspan"],
          "width" => $vCol["voucher_input_width"],
        ];
        $fmtDBFields[] = [
          "fieldName" => $vCol["db_field_name"],
          "fieldType" => $vCol["db_field_type"],
          "voucherInput" => $vCol["voucher_input"],
        ];
      }

      // 有分录附加项
      if (count($fmtCols) > 0) {
        $sql = "select ";
        foreach ($fmtDBFields as $i => $field) {
          if ($i > 0) {
            $sql .= ",";
          }

          $f = $field["fieldName"];
          if ($f == "x_v_cnt") {
            $sql .= "convert({$f}, " . $fmt . ") as {$f}";
          } else {
            $sql .= $f;
          }

          if ($field["voucherInput"] == 2) {
            // 码表，补上name字段
            $sql .= "," . $field["fieldName"] . "_name";
          }
        }
        $sql .= " from {$exTableName} where id = '%s' ";
        $data = $db->query($sql, $detailId);

        foreach ($fmtDBFields as $i => $field) {
          $fieldName = $field["fieldName"];
          $fieldType = $field["fieldType"];
          $fieldValue = $data[0][$fieldName];

          if ($fieldType == "date") {
            $fieldValue = $this->toYMD($fieldValue);
          }

          if ($field["voucherInput"] == 2) {
            // 码表
            $fieldValue = $data[0][$fieldName] . " - " . $data[0][$fieldName . "_name"];
          }

          $fmtCols[$i]["value"] = $fieldValue;
        }
      }

      $result[] = [
        "id" => $detailId,
        "subject" => $subjectCode . " - " . $subjectName,
        "summary" => $summary,
        "debit" => $debit,
        "credit" => $credit,
        "fmtCols" => $fmtCols,
      ];
    }

    return $result;
  }

  /**
   * 删除凭证
   */
  public function deleteVoucher(&$params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $sql = "select ref, company_id, company_name, v_status, created_by
            from t_voucher
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要删除的记账凭证不存在");
    }
    $v = $data[0];
    $status = $v["v_status"];
    $ref = $v["ref"];
    $companyId = $v["company_id"];
    $companyName = $v["company_name"];
    $createdBy = $v["created_by"];

    if ($status > 0) {
      return $this->bad("<span style='color:red'>{$ref}</span> 记账凭证已经复核，不能删除");
    }

    if ($createdBy > 0) {
      return $this->bad("<span style='color:red'>{$ref}</span> 记账凭证是系统自动生成的凭证，不能手工删除");
    }

    // 删除分录附加项
    $sql = "select subject
            from t_voucher_detail
            where voucher_id = '%s' 
            order by show_order";
    $detailData = $db->query($sql, $id);
    foreach ($detailData as $v) {
      $subjectCode = $v["subject"];

      // 查询账样获取分录附加项的数据库表名
      $sql = "select voucher_db_table_name
              from t_acc_fmt
              where company_id = '%s' and subject_code = '%s' ";
      $data = $db->query($sql, $companyId, $subjectCode);
      if (!$data) {
        // 出莫名的bug了
        return $this->badParam("companyId - subjectCode");
      }

      $exTableName = $data[0]["voucher_db_table_name"];

      $sql = "delete from {$exTableName} where voucher_id = '%s' ";
      $rc = $db->execute($sql, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    // 删除凭证分录
    $sql = "delete from t_voucher_detail where voucher_id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 删除凭证主表
    $sql = "delete from t_voucher where id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "删除记账凭证：凭证号 - {$ref}，组织机构 - {$companyName}";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 凭证详情
   *
   * @param array $params
   */
  public function voucherInfo($params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $result = [];

    // 凭证主表
    $sql = "select v_dt, ref, v_word, charge_user_id, charge_user_name,
              cash_user_id, cash_user_name, bill_number, biz_user_id,
              biz_user_name, company_name, v_status, company_id
            from t_voucher
            where id = '%s' ";
    $data = $db->query($sql, $id);

    if (!$data) {
      return $result;
    }

    $v = $data[0];
    $result["voucherDT"] = $this->toYMD($v["v_dt"]);
    $result["ref"] = $v["ref"];
    $word = $v["v_word"];
    if (!$word) {
      $word = "[不使用凭证字]";
    }
    $result["word"] = $word;
    $result["chargeUserId"] = $v["charge_user_id"];
    $result["chargeUserName"] = $v["charge_user_name"];
    $result["cashUserId"] = $v["cash_user_id"];
    $result["cashUserName"] = $v["cash_user_name"];
    $result["billNumber"] = $v["bill_number"];
    $result["bizUserId"] = $v["biz_user_id"];
    $result["bizUserName"] = $v["biz_user_name"];
    $result["voucherStatus"] = $v["v_status"];
    $companyId = $v["company_id"];
    $result["company"] = [
      [
        "id" => $v["company_id"],
        "name" => $v["company_name"],
      ]
    ];

    // 分录
    $items = [];
    $sql = "select v.id, v.subject, v.summary, v.debit, v.credit, s.name as subject_name
            from t_voucher_detail v, t_subject s
            where (v.voucher_id = '%s') and (v.subject = s.code) and (s.company_id = '%s')
            order by v.show_order";
    $data = $db->query($sql, $id, $companyId);
    foreach ($data as $v) {
      $detailId = $v["id"];
      $subjectCode = $v["subject"];

      // 分录附加项
      $sql = "select id, voucher_db_table_name 
              from t_acc_fmt 
              where company_id = '%s' and subject_code = '%s' ";
      $data = $db->query($sql, $companyId, $subjectCode);
      if (!$data) {
        // 执行到这里应该就是莫名的bug
        continue;
      }

      $fmtId = $data[0]["id"];
      $exTableName = $data[0]["voucher_db_table_name"];

      $fmtCols = [];
      $fmtDBFields = [];
      $sql = "select id, caption, db_field_name, db_field_type, voucher_input,
                voucher_input_colspan, voucher_input_width, voucher_input_xtype,
                code_table_name
            from t_acc_fmt_cols
            where fmt_id = '%s' and sys_col = 0 and col_category in ( 1, 0 )
            order by voucher_input_show_order ";
      $data = $db->query($sql, $fmtId);
      foreach ($data as $vCol) {
        $fmtCols[] = [
          "id" => $vCol["id"],
          "caption" => $vCol["caption"],
          "colspan" => $vCol["voucher_input_colspan"],
          "width" => $vCol["voucher_input_width"],
          "voucherInputXtype" => $vCol["voucher_input_xtype"],
        ];
        $fmtDBFields[] = [
          "fieldName" => $vCol["db_field_name"],
          "fieldType" => $vCol["db_field_type"],
          "voucherInput" => $vCol["voucher_input"],
          "codeTableName" => $vCol["code_table_name"],
        ];
      }

      // 有分录附加项
      if (count($fmtCols) > 0) {
        $sql = "select ";
        foreach ($fmtDBFields as $i => $field) {
          if ($i > 0) {
            $sql .= ",";
          }

          $sql .= $field["fieldName"];

          if ($field["voucherInput"] == 2) {
            // 码表，补上id和name字段
            $sql .= "," . $field["fieldName"] . "_name, " . $field["fieldName"] . "_id ";
          }
        }
        $sql .= " from {$exTableName} where id = '%s' ";
        $data = $db->query($sql, $detailId);

        foreach ($fmtDBFields as $i => $field) {
          $fieldName = $field["fieldName"];
          $fieldType = $field["fieldType"];
          $fieldValue = $data[0][$fieldName];

          if ($fieldType == "date") {
            $fieldValue = $this->toYMD($fieldValue);
          }

          if ($field["voucherInput"] == 2) {
            // 码表
            $fieldValue = $data[0][$fieldName] . " - " . $data[0][$fieldName . "_name"];
            $fmtCols[$i]["codeId"] = $data[0][$fieldName . "_id"];
            $fmtCols[$i]["codeValue"] = $data[0][$fieldName];
            $fmtCols[$i]["nameValue"] = $data[0][$fieldName . "_name"];

            $codeTableName = $field["codeTableName"];

            $sql = "select fid
                    from t_code_table_md
                    where table_name = '%s' ";
            $d = $db->query($sql, $codeTableName);
            $fmtCols[$i]["fid"] = $d[0]["fid"];
          }

          $fmtCols[$i]["value"] = $fieldValue;
        }
      }

      $items[] = [
        "id" => $v["id"],
        "subjectCode" => $subjectCode,
        "subjectName" => $v["subject_name"],
        "summary" => $v["summary"],
        "debit" => $v["debit"],
        "credit" => $v["credit"],
        "fmtCols" => $fmtCols,
      ];
    }

    $result["items"] = $items;

    return $result;
  }

  /**
   * 复核凭证
   */
  public function commitVoucher(&$params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $loginUserId = $params["loginUserId"];
    $loginUserName = $params["loginUserName"];

    // 主表
    $sql = "select ref, company_name, v_status, company_id
            from t_voucher
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要复核的记账凭证不存在");
    }
    $v = $data[0];

    $status = $v["v_status"];
    if ($status > 0) {
      return $this->bad("记账凭证已经复核过，不能再次复核");
    }

    $ref = $v["ref"];
    $companyName = $v["company_name"];
    $companyId = $v["company_id"];

    // 检查：是否有分录
    $items = [];
    $sql = "select id, subject, debit, credit
            from t_voucher_detail
            where voucher_id = '%s'
            order by show_order";
    $data = $db->query($sql, $id);
    foreach ($data as $v) {
      $items[] = [
        "id" => $v["id"],
        "subjectCode" => $v["subject"],
        "debit" => $v["debit"],
        "credit" => $v["credit"],
      ];
    }

    if (count($items) == 0) {
      return $this->bad("记账凭证没有录入分录");
    }

    // 检查：分录借贷金额合计是否相等
    $dbSum = 0;
    $crSum = 0;
    foreach ($items as $v) {
      $dbSum += $v["debit"];
      $crSum += $v["credit"];
    }
    if ($dbSum != $crSum) {
      return $this->bad("借方金额合计({$dbSum}元) <span style='color:red'>≠</span> 贷方金额合计({$crSum}元)");
    }

    // 检查：各个分录的附加项的数据是否都已经录入
    foreach ($items as $i => $v) {
      $detailId = $v["id"];
      $subjectCode = $v["subjectCode"];

      $sql = "select id, voucher_db_table_name
              from t_acc_fmt
              where company_id = '%s' and subject_code = '%s' ";
      $data = $db->query($sql, $companyId, $subjectCode);
      if (!$data) {
        $index = $i + 1;
        return $this->bad("第{$index}条分录的科目码[{$subjectCode}]没有对应的账样元数据");
      }
      $fmtId = $data[0]["id"];
      $exTableName = $data[0]["voucher_db_table_name"];

      $sql = "select db_field_name, caption, voucher_input
              from t_acc_fmt_cols
              where fmt_id = '%s' and sys_col = 0 and col_category != 2
              order by voucher_input_show_order";
      $exCols = $db->query($sql, $fmtId);
      if (count($exCols) == 0) {
        continue;
      }

      // 查询分录附加项数据
      $sql = "select ";
      foreach ($exCols as $idx => $x) {
        if ($idx > 0) {
          $sql .= ", ";
        }
        $sql .= $x["db_field_name"];
        if ($x["voucher_input"] == 2) {
          // 码表录入，再增加字段：_id和_name
          $fieldNameId = $x["db_field_name"] . "_id";
          $fieldNameName = $x["db_field_name"] . "_name";
          $sql .= ", {$fieldNameId}, {$fieldNameName}";
        }
      }
      $sql .= " from {$exTableName} where id = '%s' ";

      $exData = $db->query($sql, $detailId);
      if (!$exData) {
        // 莫名的bug
        return $this->bad("分录附加项数据异常");
      }

      $exRec = $exData[0];
      // 检查附加项数据是否都录入
      foreach ($exCols as $idx => $x) {
        $index = $idx + 1;
        $fieldName = $x["db_field_name"];
        $voucherInput = $x["voucher_input"];
        $caption = $x["caption"];
        $colValue = $exRec[$fieldName];

        if ($voucherInput == 1) {
          // 直接录入
          if (!$colValue) {
            $info = "第{$index}条分录的附加项[{$caption}]字段没有录入数据";
            return $this->bad($info);
          }
        } else if ($voucherInput == 2) {
          // 码表录入
          $fieldNameId = $fieldName . "_id";
          $fieldNameName = $fieldName . "_name";
          $colValueId = $exRec[$fieldNameId];
          $colValueName = $exRec[$fieldNameName];

          if (!$colValue || !$colValueId || !$colValueName) {
            $info = "第{$index}条分录的附加项[{$caption}]字段没有录入数据";
            return $this->bad($info);
          }
        }
      }
    }

    // 更改凭证状态
    $sql = "update t_voucher
            set v_status = 1000, confirm_user_id = '%s', confirm_user_name = '%s', confirm_dt = now()
            where id = '%s' ";
    $rc = $db->execute($sql, $loginUserId, $loginUserName, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "复核记账凭证：凭证号 - {$ref}，组织机构 - {$companyName}";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 取消复核凭证
   */
  public function cancelCommitVoucher(&$params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $sql = "select v_status, ref, company_name, created_by from t_voucher where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("要取消复核状态的记账凭证不存在");
    }
    $v = $data[0];
    $status = $v["v_status"];
    $ref = $v["ref"];
    $companyName = $v["company_name"];
    $createdBy = $v["created_by"];

    if ($status == 0) {
      return $this->bad("<span style='color:red'>{$ref}</span> 记账凭证尚未复核，无需取消复核状态");
    }
    if ($status == 2000) {
      return $this->bad("<span style='color:red'>{$ref}</span> 记账凭证已经记账，不能取消复核状态");
    }

    if ($createdBy == 1) {
      // 自动生成的凭证
      return $this->bad("{$ref} 凭证是系统自动生成的凭证，不能取消复核");
    }

    // 更改凭证状态
    $sql = "update t_voucher
            set v_status = 0, 
              confirm_user_id = null, confirm_user_name = null, confirm_dt = null
            where id = '%s' and v_status = 1000 ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "取消复核记账凭证：凭证号 - {$ref}，组织机构 - {$companyName}";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 凭证断号重排
   */
  public function refReorder(&$params)
  {
    $db = $this->db;

    $year = $params["year"];
    $month = $params["month"];

    $companyId = $params["companyId"];
    $companyName = $this->getCompanyNameById($companyId);
    if (!$companyName) {
      return $this->badParam("companyId");
    }

    $sql = "select id
            from t_voucher
            where company_id = '%s' 
              and v_status < 2000 
              and v_year = %d and v_month = %d ";
    $data = $db->query($sql, $companyId, $year, $month);
    if (!$data) {
      return $this->bad("凭证都已经记账，不能再调整凭证号");
    }

    $sql = "select distinct v_word
            from t_voucher
            where company_id = '%s' 
              and v_status < 2000 
              and v_year = %d and v_month = %d ";
    $wordList = $db->query($sql, $companyId, $year, $month);
    foreach ($wordList as $w) {
      // 凭证字
      $word = $w["v_word"];

      // 查询该凭证字下已经记账的最大凭证号
      $sql = "select v_ref
              from t_voucher
              where company_id = '%s' and v_word = '%s' and v_status = 2000
                and v_year = %d and v_month = %d  
              order by v_ref desc
              limit 1";
      $data = $db->query($sql, $companyId, $word, $year, $month);
      $maxRef = 0;
      if ($data) {
        $maxRef = $data[0]["v_ref"];
      }

      // 该凭证字下尚未记账的凭证号
      $sql = "select id
              from t_voucher
              where company_id = '%s' and v_word = '%s' and v_status < 2000
                and v_year = %d and v_month = %d  
              order by v_ref";
      $data = $db->query($sql, $companyId, $word, $year, $month);
      foreach ($data as $v) {
        $id = $v["id"];

        $maxRef++;
        $newRef = $maxRef;
        $newVRef = "{$word}字第{$newRef}号";

        $sql = "update t_voucher
                set v_ref = %d, ref = '%s'
                where id = '%s' ";
        $rc = $db->execute($sql, $newRef, $newVRef, $id);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      }
    }

    // 业务日志
    $log = "[{$companyName}] - 凭证断号重排";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  private function getCompanyNameById($companyId)
  {
    $db = $this->db;
    $sql = "select name 
      from t_org 
      where id = '%s' and parent_id is null";

    $data = $db->query($sql, $companyId);
    if (!$data) {
      return null;
    } else {
      return $data[0]["name"];
    }
  }

  /**
   * 返回所有的公司列表
   *
   * @return array
   */
  public function companyList($params)
  {
    $db = $this->db;

    $dao = new OrgDAO($db);
    $data = $dao->getCompanyExList($params);

    $daoComm = new AccCommDAO($db);

    $result = [];
    foreach ($data as $v) {
      $companyId = $v["id"];

      // 查询当前会计期间
      $r = $daoComm->getCurrentAccYearAndMonth($companyId);
      $currentAccYear = null;
      $currentAccMonth = null;
      if ($r) {
        $currentAccYear = $r["accYear"];
        $currentAccMonth = $r["accMonth"];
      }

      $result[] = [
        "id" => $companyId,
        "name" => $v["name"],
        "currentAccYear" => $currentAccYear,
        "currentAccMonth" => $currentAccMonth,
      ];
    }

    return $result;
  }
}
